﻿using System.ComponentModel;
using System.Data.OleDb;
using Entity;

namespace DAO
{
    public class NguoiDungDAO
    {
        static string connectionString = new connection().getConnectionString();


        public BindingList<NguoiDung> LoadAll()
        {

            BindingList<NguoiDung> list = new BindingList<NguoiDung>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from NguoiDung;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                NguoiDung kv = new NguoiDung();
                kv.MaNguoiDung = (int)reader["MaNguoiDung"];
                kv.TenDangNhap = (string)reader["TenDangNhap"];
                kv.MatKhau = (string)reader["MatKhau"];
                kv.HoVaTen = (string)reader["HoVaTen"];
                kv.DiaChi = (string)reader["DiaChi"];
                kv.Email = (string)reader["Email"];
                kv.PhanQuyen = (string)reader["PhanQuyen"];
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list;
        }

        public NguoiDung LoadByID(int id)
        {

            BindingList<NguoiDung> list = new BindingList<NguoiDung>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from NguoiDung where MaNguoiDung = @MaNguoiDung;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaNguoiDung", OleDbType.VarChar).Value = id;
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                NguoiDung kv = new NguoiDung();
                kv.MaNguoiDung = (int)reader["MaNguoiDung"];
                kv.TenDangNhap = (string)reader["TenDangNhap"];
                kv.MatKhau = (string)reader["MatKhau"];
                kv.HoVaTen = (string)reader["HoVaTen"];
                kv.DiaChi = (string)reader["DiaChi"];
                kv.Email = (string)reader["Email"];
                kv.PhanQuyen = (string)reader["PhanQuyen"];
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list[0];
        }
        public int DeleteByStringId(int id)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "DELETE FROM NguoiDung WHERE MaNguoiDung = @MaNguoiDung;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaNguoiDung", OleDbType.VarChar).Value = id;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();

            return result;
        }
        public NguoiDung Login(string tendangnhap, string matkhau) {
            BindingList<NguoiDung> list = new BindingList<NguoiDung>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select TOP 1 * from NguoiDung where tendangnhap = @tendangnhap AND matkhau= @matkhau";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@TenDangNhap", OleDbType.VarChar).Value = tendangnhap;
            command.Parameters.Add("@MatKhau", OleDbType.VarChar).Value = matkhau;
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                NguoiDung kv = new NguoiDung();
                kv.MaNguoiDung = (int)reader["MaNguoiDung"];
                kv.TenDangNhap = (string)reader["TenDangNhap"];
                kv.MatKhau = (string)reader["MatKhau"];
                kv.HoVaTen = (string)reader["HoVaTen"];
                kv.DiaChi = (string)reader["DiaChi"];
                kv.Email = (string)reader["Email"];
                kv.PhanQuyen = (string)reader["PhanQuyen"];
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list[0];
        }
        public int Insert(NguoiDung kv)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "INSERT INTO NguoiDung ([TenDangNhap],[MatKhau],[HoVaTen],[DiaChi],[Email],[PhanQuyen]) VALUES(@TenDangNhap,@MatKhau,@HoVaTen,@DiaChi,@Email,@PhanQuyen);";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@TenDangNhap", OleDbType.VarChar).Value = kv.TenDangNhap;
            command.Parameters.Add("@MatKhau", OleDbType.VarChar).Value = kv.MatKhau;
            command.Parameters.Add("@HoVaTen", OleDbType.VarChar).Value = kv.HoVaTen;
            command.Parameters.Add("@DiaChi", OleDbType.VarChar).Value = kv.DiaChi;
            command.Parameters.Add("@Email", OleDbType.VarChar).Value = kv.Email;
            command.Parameters.Add("@PhanQuyen", OleDbType.VarChar).Value = kv.PhanQuyen;

            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();
            return result;
        }
        public int Update(NguoiDung kv)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "UPDATE NguoiDung   SET [TenDangNhap] = @TenDangNhap      ,[MatKhau] = @MatKhau      ,[HoVaTen] = @HoVaTen      ,[DiaChi] = @DiaChi      ,[Email] = @Email      ,[PhanQuyen] = @PhanQuyen WHERE MaNguoiDung = @MaNguoiDung;";

            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@TenDangNhap", OleDbType.VarChar).Value = kv.TenDangNhap;
            command.Parameters.Add("@MatKhau", OleDbType.VarChar).Value = kv.MatKhau;
            command.Parameters.Add("@HoVaTen", OleDbType.VarChar).Value = kv.HoVaTen;
            command.Parameters.Add("@DiaChi", OleDbType.VarChar).Value = kv.DiaChi;
            command.Parameters.Add("@Email", OleDbType.VarChar).Value = kv.Email;
            command.Parameters.Add("@PhanQuyen", OleDbType.VarChar).Value = kv.PhanQuyen;

            command.Parameters.Add("@MaNguoiDung", OleDbType.VarChar).Value = kv.MaNguoiDung;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();
            return result;
        }
    }
}
